package dao.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import transferobject.Item;
import dao.ItemDao;

/**
 * Item DAO for Oracle database
 * @author Ilya_Poliakov
 */
public class OracleItemDao implements ItemDao {

	private static final String ADD_ITEM_QUERY = "INSERT INTO items VALUES (null, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String ALL_ITEMS_SELECTION_QUERY = "SELECT * FROM items";
	private static final String BID_INCREMENT_FIELD_NAME = "bid_increment";
	private static final String BUY_IT_NOW_FIELD_NAME = "buy_it_now";
	private static final String DELETE_ITEM_QUERY = "DELETE FROM items WHERE item_id = ?";
	private static final String DESCRIPTION_FIELD_NAME = "description";
	private static final String FIND_ITEM_BY_DESC_QUERY = "SELECT * FROM items WHERE description LIKE ?";
	private static final String FIND_ITEM_BY_TITLE_QUERY = "SELECT * FROM items WHERE title LIKE ?";
	private static final String FIND_ITEM_QUERY = "SELECT * FROM items WHERE item_id = ?";
	private static final String ITEM_ID_FIELD_NAME = "item_id";
	private static final String SELLER_ID_FIELD_NAME = "seller_id";
	private static final char SQL_BORDER = '%';
	private static final String START_BIDDING_DATE_FIELD_NAME = "start_bidding_date";
	private static final String START_PRICE_FIELD_NAME = "start_price";
	private static final String TIME_LEFT_FIELD_NAME = "time_left";
	private static final String TITLE_FIELD_NAME = "title";
	private static final String UPDATE_ITEM_QUERY = "UPDATE items SET seller_id = ?, "
			+ "title = ?, description = ?, start_price = ?, time_left = ?, start_bidding_date = ?,"
			+ "buy_it_now = ?, bid_increment = ? WHERE item_id = ?";
	private final OracleConnection connection;

	/**
	 * create new OracleBidDao
	 * @param connection OracleConnection object
	 */
	public OracleItemDao(OracleConnection connection) {
		super();
		this.connection = connection;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean deleteItem(int itemId) {
		boolean result = false;
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn.prepareStatement(DELETE_ITEM_QUERY);
			ps.setInt(1, itemId);
			result = ps.executeUpdate() > 0;
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean deleteItem(Item item) {
		boolean result = false;
		if (item != null)
			result = deleteItem(item.getItemId());
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public Item findItemById(int itemId) {
		Item item = null;
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn.prepareStatement(FIND_ITEM_QUERY);
			ps.setInt(1, itemId);
			ResultSet rs = ps.executeQuery();
			item = getItemFromResultSet(rs);
			rs.close();
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return item;
	}

	/**
	 * {@inheritDoc}
	 */
	public ArrayList<Item> findItemsByDescription(String description) {
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn.prepareStatement(FIND_ITEM_BY_DESC_QUERY);
			ps.setString(1, SQL_BORDER + description + SQL_BORDER);
			ResultSet rs = ps.executeQuery();
			Item item;
			while ((item = getItemFromResultSet(rs)) != null) {
				items.add(item);
			}
			rs.close();
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return items;
	}

	/**
	 * {@inheritDoc}
	 */
	public ArrayList<Item> findItemsByTitle(String title) {
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn
					.prepareStatement(FIND_ITEM_BY_TITLE_QUERY);
			ps.setString(1, SQL_BORDER + title + SQL_BORDER);
			ResultSet rs = ps.executeQuery();
			Item item;
			while ((item = getItemFromResultSet(rs)) != null) {
				items.add(item);
			}
			rs.close();
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return items;
	}

	/**
	 * {@inheritDoc}
	 */
	public ArrayList<Item> getAllItems() {
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			Connection cn = connection.getConnection();
			Statement stmt = cn.createStatement();
			ResultSet rs = stmt.executeQuery(ALL_ITEMS_SELECTION_QUERY);
			Item item;
			while ((item = getItemFromResultSet(rs)) != null) {
				items.add(item);
			}
			rs.close();
			stmt.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return items;
	}

	/*
	 * Return next Item from ResultSet
	 * @param rs ResultSet
	 * @return Item
	 */
	private Item getItemFromResultSet(ResultSet rs) {
		Item item = null;
		try {
			if (rs.next()) {
				item = new Item(rs.getInt(ITEM_ID_FIELD_NAME),
						rs.getInt(SELLER_ID_FIELD_NAME),
						rs.getString(TITLE_FIELD_NAME),
						rs.getString(DESCRIPTION_FIELD_NAME),
						rs.getFloat(START_PRICE_FIELD_NAME),
						rs.getInt(TIME_LEFT_FIELD_NAME),
						rs.getTimestamp(START_BIDDING_DATE_FIELD_NAME),
						rs.getInt(BUY_IT_NOW_FIELD_NAME) == 1,
						rs.getFloat(BID_INCREMENT_FIELD_NAME));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return item;
	}

	/**
	 * {@inheritDoc}
	 */
	public int insertItem(Item item) {
		int result = 0;
		try {
			Connection cn = connection.getConnection();
			String[] fields = new String[] { ITEM_ID_FIELD_NAME };
			PreparedStatement ps = cn.prepareStatement(ADD_ITEM_QUERY, fields);
			ps.setInt(1, item.getSellerId());
			ps.setString(2, item.title);
			ps.setString(3, item.getDescription());
			ps.setFloat(4, item.getStartPrice());
			ps.setInt(5, item.getTimeLeft());
			ps.setTimestamp(6, new java.sql.Timestamp(item
					.getStartBiddingDate().getTime()));
			ps.setInt(7, item.isBuyItNow() ? 1 : 0);
			ps.setFloat(8, item.getBidIncrement());
			ps.executeUpdate();
			ResultSet generatedKeys = ps.getGeneratedKeys();
			if (generatedKeys.next()) {
				result = (int) generatedKeys.getLong(1);
				item.setItemId(result);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean updateItem(Item item) {
		boolean result = false;
		try {
			PreparedStatement ps = connection.getConnection().prepareStatement(
					UPDATE_ITEM_QUERY);
			ps.setInt(1, item.getSellerId());
			ps.setString(2, item.title);
			ps.setString(3, item.getDescription());
			ps.setFloat(4, item.getStartPrice());
			ps.setInt(5, item.getTimeLeft());
			ps.setTimestamp(6, new java.sql.Timestamp(item
					.getStartBiddingDate().getTime()));
			ps.setInt(7, item.isBuyItNow() ? 1 : 0);
			ps.setFloat(8, item.getBidIncrement());
			ps.setInt(9, item.getItemId());
			result = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

}
